<?php
/**
 * Created by JetBrains PhpStorm.
 * User: VAN HUNG
 * Date: 9/16/12
 * Time: 8:22 PM
 * To change this template use File | Settings | File Templates.
 */
class Mgenre extends CI_Model {
    public  function __construct() {
        parent::__construct();
        $this->load->database();
    }
    public function getAllGenre() {
         $result=$this->db->query("SELECT idGenre,strGenre FROM genre");
         return $result->result_array();
    }
    public function getGenre($id) {
         $result=$this->db->query("SELECT strGenre FROM genre WHERE idGenre=$id");
         return $result->result_array();
    }
    public function getAllAlbumGenre($id) {
    	 $result=$this->db->query(" SELECT album.idAlbum,album.idTheme,
    	 	album.idArtist,album.strNameAlbum,album.strImage,album.strReview,album.iYear,album.iRating,
			album.strExtraArtist,artist.strArtist,
			genre.strGenre,genre.idGenre
		FROM genre 
		INNER JOIN album ON CONVERT(genre.idGenre USING utf8) =CONVERT(album.idGenre USING utf8)
		INNER JOIN artist ON CONVERT(album.idArtist USING utf8)=CONVERT(artist.idArtist USING utf8) WHERE genre.idGenre LIKE '$id'");
        return $result->result_array();
    }
     public function getAlbumGenre($id,$limit) {
    	 $result=$this->db->query(" SELECT album.idAlbum,album.idTheme,
    	 	album.idArtist,album.strNameAlbum,album.strImage,album.strReview,album.iYear,album.iRating,
			album.strExtraArtist,artist.strArtist,
			genre.strGenre,genre.idGenre
		FROM genre 
		INNER JOIN album ON CONVERT(genre.idGenre USING utf8) =CONVERT(album.idGenre USING utf8)
		INNER JOIN artist ON CONVERT(album.idArtist USING utf8)=CONVERT(artist.idArtist USING utf8) WHERE genre.idGenre LIKE '$id' 
		ORDER BY album.iRating DESC
		LIMIT 0,$limit");
        return $result->result_array();
    }
    public function getSongGenre($id,$limit) {
    	 $result=$this->db->query(" SELECT song.idSong,song.strFileName,song.idArtist,song.strExtraArtist,song.idTheme,song.idAuthor,song.rating,genre.idGenre,genre.strGenre,artist.strArtist,theme.strTheme,author.strNameAuthor
		FROM genre 
		INNER JOIN song ON CONVERT(genre.idGenre USING utf8) =CONVERT(song.idGenre USING utf8)
		LEFT JOIN artist ON CONVERT(song.idArtist USING utf8)=CONVERT(artist.idArtist USING utf8) 
		LEFT JOIN theme ON CONVERT(song.idTheme USING utf8)=CONVERT(theme.idTheme USING utf8)
		LEFT JOIN author ON CONVERT(song.idAuthor USING utf8)=CONVERT(author.idAuthor USING utf8)
		WHERE genre.idGenre LIKE '$id' 

		ORDER BY song.rating DESC
		LIMIT 0,$limit");
        return $result->result_array();
    }
    public function getPlistGenre($id,$limit) {
        $result=$this->db->query(" SELECT userplaylist.idPlaylist,userplaylist.strNamePList,userplaylist.strPListImage,users.strName,userplaylist.rating,genre.idGenre,genre.strGenre
        FROM genre 
        INNER JOIN userplaylist ON CONVERT(genre.idGenre USING utf8) =CONVERT(userplaylist.idGenre USING utf8)
        INNER JOIN users ON CONVERT(userplaylist.idUser USING utf8) =CONVERT(users.idUser USING utf8)
        WHERE genre.idGenre LIKE '$id' 
        ORDER BY userplaylist.rating DESC
        LIMIT 0,$limit");
        return $result->result_array();
    }
    
}
